package com.idbk.chargestation.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * 数据库操作
 * @author lupc
 *
 */
public class DBHelper extends SQLiteOpenHelper {

	/* 数据库名称  */
	public final static String DB_NAME = "my.db";

	/**
	 * 数据库版本
	 * 1：只包含消息表格
	 * 2：增加搜索历史记录表格
	 */
	private final static int VERSION = 3;

	public final static String TABLE_NAME_MESSAGE = "tbMessage";

	/* 创建 消息表格的sql语句  */
	private final static String SQL_CREATE_MESSAGE_TABLE = 
			"create table if not exists "
					+ TABLE_NAME_MESSAGE + " ("
					+ "id integer primary key autoincrement not null,"
					+ "username varchar(20) not null,"
					+ "dtime varchar(20) not null,"
					+ "type integer not null,"
					+ "isRead integer not null," 
					+ "message text not null);";
	
	private final static String SQL_CREATE_MESSAGE_INDEX_USERNAME = 
			"create index if not exists idx_username on "
			+ TABLE_NAME_MESSAGE 
			+ "(username);";
	
	
	public final static String TABLE_NAME_SEARCH_HISTORY = "tbSearchHistory";
	
	private final static String SQL_CREATE_SEARCH_HISTORY_TABLE = 
			"create table if not exists "
					+ TABLE_NAME_SEARCH_HISTORY + " ("
					+ "id integer primary key autoincrement not null,"
					+ "keyword varchar(20) not null"
					+ ");";
	
	private final static String SQL_CREATE_SEARCH_HISTORY_UNIQUE_INDEX_KEYWORD = 
			"create unique index if not exists idx_keyword on "
			+ TABLE_NAME_SEARCH_HISTORY 
			+ "(keyword);";
	
	public DBHelper(Context context) {
		super(context, DB_NAME, null, VERSION);

	}

	//该方法没有数据库存在才会执行
	@Override
	public void onCreate(SQLiteDatabase db) {
		//创建 消息表格
		db.execSQL(SQL_CREATE_MESSAGE_TABLE);
		db.execSQL(SQL_CREATE_MESSAGE_INDEX_USERNAME);
		//创建 搜索历史表格
		db.execSQL(SQL_CREATE_SEARCH_HISTORY_TABLE);
		db.execSQL(SQL_CREATE_SEARCH_HISTORY_UNIQUE_INDEX_KEYWORD);
	}

	//数据库存更新才会执行
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if(newVersion == 3){
			db.execSQL(SQL_CREATE_SEARCH_HISTORY_TABLE);
			db.execSQL(SQL_CREATE_SEARCH_HISTORY_UNIQUE_INDEX_KEYWORD);
		}
	}

}
